﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Data;

namespace KAs.Utils.Excel;

public static class ExcelUtils
{
    /// <summary>
    /// 从Excel读取数据
    /// </summary>
    /// <returns></returns>
    public static DataSet ReadFromExcel(string excelPath, int hearIndex = 0, int sheetCount = 1, bool hasHeadRowFlag = true, int colCnt = 0)
    {
        using var stream = new FileStream(excelPath, FileMode.Open, FileAccess.Read);
        return ReadFromExcel(stream, Path.GetExtension(excelPath) == ".xlsx" ? ExcelVersionEnum.Xslx : ExcelVersionEnum.Xsl, hearIndex, sheetCount, hasHeadRowFlag, colCnt);
    }

    /// <summary>
    /// 从excel读取数据
    /// </summary>
    /// <returns></returns>
    public static DataSet ReadFromExcel(Stream stream, ExcelVersionEnum excelVersion, int hearIndex = 0, int sheetCount = 1, bool hasHeadRowFlag = true, int colCnt = 0)
    {
        IWorkbook workbook = excelVersion == ExcelVersionEnum.Xslx ? new XSSFWorkbook(stream) : new HSSFWorkbook(stream);
        sheetCount = Math.Min(workbook.NumberOfSheets, sheetCount);
        var rtnDs = new DataSet();
        for (int i = 0; i < sheetCount; i++)
        {
            var sheet = workbook.GetSheetAt(i);
            DataTable dt = SheetToDataTable(sheet, hearIndex, hasHeadRowFlag, colCnt);
            if (dt != null)
            {
                dt.TableName = sheet.SheetName;
                rtnDs.Tables.Add(dt.Copy());
            }
        }
        return rtnDs;
    }

    /// <summary>
    /// 单sheet页数据读取成DataTable
    /// </summary>
    /// <returns></returns>
    private static DataTable SheetToDataTable(ISheet sheet, int hearIndex = 0, bool hasHeadRowFlag = true, int colCnt = 0)
    {

        DataTable dt = new();

        IRow headerRow = sheet.GetRow(hearIndex);
        if (headerRow.Cells.Count <= 0) return dt;

        if (hasHeadRowFlag)
        {
            foreach (var item in headerRow.Cells)
                if (colCnt > dt.Columns.Count)
                    dt.Columns.Add(item.StringCellValue);
            while (colCnt > dt.Columns.Count)
                dt.Columns.Add(new DataColumn());
        }
        else
        {
            var tIndex = 0;
            foreach (var item in headerRow.Cells)
            {
                tIndex++;
                dt.Columns.Add(tIndex.ToString());
            }
        }

        // write the rest
        int rowIndex = 0;
        foreach (IRow row in sheet)
        {
            if (rowIndex++ < hearIndex + 1) continue;
            DataRow dataRow = dt.NewRow();
            dataRow.ItemArray = row.Cells.FindAll(x => x.ColumnIndex < dt.Columns.Count).Select(c => c.ToString()).ToArray();
            dt.Rows.Add(dataRow);
        }
        return dt;
    }

    // 写
    public static void WriteToExcel()
    {

    }
}
